package com.guaoran.study.odps;

import java.sql.*;

import static com.guaoran.study.odps.OppsDemo.getNetFileSizeDescription;

/**
 * @author : gu cheng
 * @description : mysql
 * @date : 2021/5/20 18:04
 */
public class MysqlDemo {
    private static final String mySqlDriver = "com.mysql.jdbc.Driver";
    private static final String TABLE       = "TABLE";
    private static final String REMARKS     = "REMARKS";
    private static final String TABLE_NAME  = "TABLE_NAME";
    private static final String COLUMN_NAME = "COLUMN_NAME";
    private static final String TYPE_NAME   = "TYPE_NAME";
    private static final String TABLE_CAT   = "TABLE_CAT";

    private Connection getConnect(MySqlDataSourceConfigDTO dto) throws Exception {
        Class.forName(mySqlDriver);
        String url = "jdbc:mysql://" + dto.getUrl() + ":" + dto.getPort() + "/" + dto.getDatabaseName()
                + "?useInformationSchema=true&useSSL=false";
        Connection connection = DriverManager.getConnection(url, dto.getUsername(), dto.getPassword());
        return connection;
    }

    public void testConnect(String dataSourceConfig) {
        MySqlDataSourceConfigDTO dto = new MySqlDataSourceConfigDTO();
        try {
            getConnect(dto);
        } catch (Exception e) {
        }
    }

    /**
     * 获取元数据
     * @throws Exception
     */
    public void getMetaData(MySqlDataSourceConfigDTO dto) throws Exception{
        Connection connection = getConnect(dto);
        DatabaseMetaData metaData = connection.getMetaData();
        // 获取表信息
        ResultSet resultSet = metaData.getTables(dto.getDatabaseName(), null, "custom_%", new String[] { TABLE });
        while (resultSet.next()) {
            String tableName = resultSet.getString(TABLE_NAME);
            String dataSourceName = resultSet.getString(TABLE_CAT);
            String desc = resultSet.getString(REMARKS);
            ResultSet rs = connection.getMetaData().getColumns(dto.getDatabaseName(), null, tableName.toUpperCase(), "%");
            while (rs.next()) {
                String name = rs.getString(COLUMN_NAME);
                String comment = rs.getString(REMARKS);
                String type = rs.getString(TYPE_NAME);
                System.out.println("dataSourceName="+dataSourceName+"\ttableName="+tableName+"\ttableComment="+desc +"\tcolumnName="+name+"\tcomment="+comment+"\ttype="+type);

            }

        }
    }



    /**
     * 获取存储量
     *
     * @return
     */
    protected String getStorageCapacity() throws Exception{
        String sql = "SELECT data_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND table_name=? ;";
        Connection connection = getConnect(null);
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,"");
        ps.setString(2,"");
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            long dataLength = resultSet.getLong("data_length");
            return getNetFileSizeDescription(dataLength);
        }
        return null;
    }


    public static void main(String[] args) throws Exception {
        // #druid config
        //spring.druid.driverClassName=com.mysql.jdbc.Driver
        //spring.druid.url=jdbc:mysql://rm-8vbs6givq78c5rf52oo.mysql.zhangbei.rds.aliyuncs.com:3306/sunyur_adb?allowMultiQueries=true&useUnicode=true&characterEncodeing=UTF-8&useSSL=false
        //#spring.druid.url=jdbc:mysql://172.18.0.227:3306/product_db?allowMultiQueries=true&useUnicode=true&characterEncodeing=UTF-8&useSSL=false
        //spring.druid.username=root
        //spring.druid.password=Shangyue@123
        //#spring.druid.password=123456
        //spring.druid.MaxActive=30
        //spring.druid.minIdle=5
        //spring.druid.maxWait=10000
        //spring.druid.validationQuery=SELECT 'x'
        //spring.druid.timeBetweenEvictionRunsMillis=60000
        //spring.druid.minEvictableIdleTimeMillis=300000



        MysqlDemo demo = new MysqlDemo();
        MySqlDataSourceConfigDTO dto  = new MySqlDataSourceConfigDTO();
        dto.setDatabaseName("sunyur_adb");
        dto.setUrl("rm-8vbs6givq78c5rf52oo.mysql.zhangbei.rds.aliyuncs.com");
        dto.setPort(3306);
        dto.setUsername("root");
        dto.setPassword("Shangyue@123");
        demo.getMetaData(dto);
    }
}
